/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package SubModule;
import Module.ModuleBean;
import java.sql.*;
import java.util.Vector;

/**
 *
 * @author mishra
 */
public class Query {
    static Connection con=null;
    
    public static int add(SubModuleBean smb) throws SQLException
    {
        int i,k=0;
        try
        {
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("insert into sub_module values(submoduleid.nextval,?,?,sysdate)");
            pre.setInt(1, smb.getModuleId());
            pre.setString(2, smb.getSubModuleName());
            i=pre.executeUpdate();
            if(i!=0)
            {
                k=getMaxId();
            }
        }
        catch(Exception e)
        {
            System.out.println(e);
        }
        finally{
            con.close();
        }
        return k;
    }
    
    public static int getMaxId() throws SQLException
    {
        int i=0;
        try
        {
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("select max(sub_module_id) from sub_module");
            ResultSet rs=pre.executeQuery();
            while(rs.next())
            {
                i=rs.getInt(1);
            }
            
        }
        catch(Exception e)
        {
            System.out.println(e);
        }
        return i;
    }
public static SubModuleBean viewSpecific(String cname, int val) throws SQLException
{
    SubModuleBean sub=new SubModuleBean();
  
    try
    {
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from sub_module where "+cname+"=?");
        pre.setInt(1, val);
        ResultSet rs=pre.executeQuery();
        while(rs.next())
        {
            sub.setSubModuleId(rs.getInt(1));
            sub.setSubModuleName(rs.getString(2));
            sub.setModuleId(rs.getInt(3));
            sub.setEntryDate(rs.getDate(4));
        }
    }
    catch(Exception e)
    {
        System.out.println(e);
    }finally{
    con.close();
    }
    return sub;
}
    

public static Vector viewAll(int id) throws SQLException
{
    Vector<Object> list=new Vector<Object>();
    
    try
    {
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from sub_module where module_id=? order by sub_module_id");
        pre.setInt(1, id);
        ResultSet rs=pre.executeQuery();
        while(rs.next())
        {
            SubModuleBean sub=new SubModuleBean();
            sub.setSubModuleId(rs.getInt(1));
            sub.setSubModuleName(rs.getString(2));
            sub.setModuleId(rs.getInt(3));
            sub.setEntryDate(rs.getDate(4));
            list.add(sub);
        }
    }
    catch(Exception e)
    {
        System.out.println(e);
    }finally{
    con.close();
    }
    return list;
}

public static Vector viewAll() throws SQLException
{
    Vector<Object> list=new Vector<Object>();
    
    try
    {
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from sub_module order by sub_module_id");
        ResultSet rs=pre.executeQuery();
        while(rs.next())
        {
            SubModuleBean sub=new SubModuleBean();
            sub.setSubModuleId(rs.getInt(1));
            sub.setSubModuleName(rs.getString(2));
            sub.setModuleId(rs.getInt(3));
            sub.setEntryDate(rs.getDate(4));
            list.add(sub);
        }
    }
    catch(Exception e)
    {
        System.out.println(e);
    }finally{
    con.close();
    }
    return list;
}

public static int edit(SubModuleBean smb) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("update sub_module set module_id=?, sub_module_name=?, entrydate=sysdate where sub_module_id=?");
            pre.setInt( 1, smb.getModuleId());
            pre.setString(2, smb.getSubModuleName());
            pre.setInt(3, smb.getSubModuleId());
            i = pre.executeUpdate();

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }
    public static int delete(int id) throws SQLException
        {
            int i=0;
            try
            {
              con=DBConnection.Connection.getConnection();
              PreparedStatement pre=con.prepareStatement("delete from sub_module where sub_module_id=?");
              pre.setInt(1,id);
              i=pre.executeUpdate();
            }
            catch(Exception e)
            {
               return i; 
            }
            finally
            {
              con.close();  
            }
            return i;
        }  
}
